/**
 * 
 */
package com.yunyike.utils;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.POIXMLDocumentPart;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFPicture;
import org.apache.poi.hssf.usermodel.HSSFPictureData;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFShape;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.PictureData;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFPicture;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFShape;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;

import com.yunyike.constants.ExcelConstants;


public class ExcelUtils {

	  /*************************** 表格相关 *************************/
    public static Map<String, Object> geneHSSFBookBegin(String workName, String[] headTitle) {
        Map<String, Object> map = new LinkedHashMap<String, Object>();
        HSSFWorkbook workbook = new HSSFWorkbook();// 声明一个工作薄
        HSSFSheet sheet = workbook.createSheet(workName);// 生成一个表格
        HSSFRow row = sheet.createRow((int) 0);
        HSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setFillForegroundColor(HSSFColor.YELLOW.index);
        sheet.setDefaultColumnWidth(20);
        HSSFCell cell = null;
        for (int i = 0; i < headTitle.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(headTitle[i]);
            cell.setCellStyle(style);
            
        }
        map.put("workbook", workbook);
        map.put("sheet", sheet);
        map.put("row", row);
        return map;
    }
    public static Map<String, Object> geneXSSFBookBegin(String workName, String[] headTitle) {
        Map<String, Object> map = new LinkedHashMap<String, Object>();
        XSSFWorkbook workbook = new XSSFWorkbook();// 声明一个工作薄
        XSSFSheet sheet = workbook.createSheet(workName);// 生成一个表格
        XSSFRow row = sheet.createRow((int) 0);
        XSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
        style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);
        style.setFillForegroundColor(HSSFColor.YELLOW.index);
        sheet.setDefaultColumnWidth(20);
        XSSFCell cell = null;
        for (int i = 0; i < headTitle.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(headTitle[i]);
            cell.setCellStyle(style);
        }
        map.put("workbook", workbook);
        map.put("sheet", sheet);
        map.put("row", row);
        return map;
    }

    public static Map<String, Object> geneBookBeginDouble(String workName, String[] headTitle, String[] fistTitle,
            int[] spanArr) {
        Map<String, Object> map = new LinkedHashMap<String, Object>();
        HSSFWorkbook workbook = new HSSFWorkbook();// 声明一个工作薄
        HSSFSheet sheet = workbook.createSheet(workName);// 生成一个表格
        HSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setFillForegroundColor(HSSFColor.YELLOW.index);
        HSSFCellStyle style0 = workbook.createCellStyle();
        style0.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
        sheet.setDefaultColumnWidth(20);
        HSSFRow row = sheet.createRow(0);
        HSSFCell cell = null;
        for (int i = 0; i < headTitle.length; i++) {
            cell = row.createCell(i);
            cell.setCellStyle(style0);
        }
        if (spanArr != null && spanArr.length > 0) {
            int cellNum = 0;
            for (int i = 0; i < spanArr.length; i++) {
                int cellEnd = spanArr[i] - 1;
                addMergedRegion(sheet,0, 0, cellNum, cellNum + cellEnd, style);
                sheet.getRow(0).getCell(cellNum).setCellValue(fistTitle[i]);
                cellNum = cellNum + spanArr[i];
            }
        }
        row = sheet.createRow(1);
        for (int i = 0; i < headTitle.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(headTitle[i]);
            cell.setCellStyle(style);
        }
        map.put("workbook", workbook);
        map.put("sheet", sheet);
        map.put("row", row);
        return map;
    }

    public static void writeBookEnd(String workName, HttpServletResponse response, Workbook workbook)
            throws Exception {
    	OutputStream os = response.getOutputStream();
        response.reset();
        response.setCharacterEncoding("UTF-8");
        workName = java.net.URLEncoder.encode(workName, "UTF-8");
        response.setHeader("Content-Disposition", "attachment;filename="
                + new String(workName.getBytes("UTF-8"), "GBK"));
        response.setContentType("application/msexcel");// 定义输出类型
        workbook.write(os);
        os.flush();
        os.close();
    }

    /**
     * 生成单元格的列
     * 
     * @param row
     * @param cellNum
     * @param cellValue
     * @param styleRow
     */
    @SuppressWarnings("deprecation")
    public static void geneCell(Row row, Short cellNum, String cellValue, CellStyle styleRow) {
        Cell cell = row.createCell(cellNum);
        cell.setCellValue(cellValue);
        cell.setCellStyle(styleRow);
    }

    @SuppressWarnings("deprecation")
    public static void addMergedRegion(HSSFSheet sheet, int rowNumStart, int rowNumEnd, int celStart, int celEnd,
            HSSFCellStyle styleRow) {
        CellRangeAddress cellRangeAddress = new CellRangeAddress(rowNumStart, rowNumEnd, celStart, celEnd);
        sheet.addMergedRegion(cellRangeAddress);
        // styleRow.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//
        // 垂直居中对齐
        // sheet.getRow(rowNumStart).getCell(celStart).setCellStyle(styleRow);
    }

    /**
     * 得到Excel表中的值
     * 
     * @param hssfCell
     *            Excel中的每一个格子
     * @return Excel中每一个格子中的值
     */
    public static String getValue(Row hssfRow,int column) {
    	Cell hssfCell = hssfRow.getCell(column);
    	if(null ==hssfCell){
    		return "";
    	}
        if (hssfCell.CELL_TYPE_BOOLEAN == hssfCell.getCellType()) {
            // 返回布尔类型的值
            return String.valueOf(hssfCell.getBooleanCellValue());
        } else if (hssfCell.CELL_TYPE_NUMERIC == hssfCell.getCellType()) {
            // 返回数值类型的值
            return String.valueOf(hssfCell.getNumericCellValue());
        } else {
        	hssfCell.setCellType(Cell.CELL_TYPE_STRING);
            // 返回字符串类型的值
        	if(null == hssfCell.getStringCellValue())return "";
            return String.valueOf(hssfCell.getStringCellValue());
        }
    	
    }
	
	/** 
     * 获取Excel2003图片 
     * @param sheetNum 当前sheet编号 
     * @param sheet 当前sheet对象 
     * @param workbook 工作簿对象 
     * @return Map key:图片单元格索引（0_1_1）String，value:图片流PictureData 
     * @throws IOException 
     */  
    public static Map<String, PictureData> getSheetPictrues03(int sheetNum,  
            HSSFSheet sheet, HSSFWorkbook workbook,String excelName) throws IOException {  
  
        Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>();  
        List<HSSFPictureData> pictures = workbook.getAllPictures();  
        if (pictures.size() != 0) {  
            for (HSSFShape shape : sheet.getDrawingPatriarch().getChildren()) {  
                HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();  
                if (shape instanceof HSSFPicture) {  
                    HSSFPicture pic = (HSSFPicture) shape;  
                    int pictureIndex = pic.getPictureIndex() - 1;  
                    HSSFPictureData picData = pictures.get(pictureIndex);  
                    String picIndex = String.valueOf(sheetNum) + "_"  
                            + String.valueOf(anchor.getRow1()) + "_"  
                            + String.valueOf(anchor.getCol1()); 
                    sheetIndexPicMap.put(picIndex, picData);
                }  
            }  
            return sheetIndexPicMap;  
        } else {  
            return null;  
        }  
    }  
  
    /** 
     * 获取Excel2007图片 
     * @param sheetNum 当前sheet编号 
     * @param sheet 当前sheet对象 
     * @param workbook 工作簿对象 
     * @return Map key:图片单元格索引（0_1_1）String，value:图片流PictureData 
     * @throws IOException 
     */  
    public static Map<String, PictureData> getSheetPictrues07(int sheetNum,  
            XSSFSheet sheet, XSSFWorkbook workbook,String excelName) throws IOException {  
        Map<String, PictureData> sheetIndexPicMap = new HashMap<String, PictureData>();  
        
        for (POIXMLDocumentPart dr : sheet.getRelations()) {  
            if (dr instanceof XSSFDrawing) {  
                XSSFDrawing drawing = (XSSFDrawing) dr;  
                List<XSSFShape> shapes = drawing.getShapes();  
                for (XSSFShape shape : shapes) {  
                    XSSFPicture pic = (XSSFPicture) shape;  
                    XSSFClientAnchor anchor = pic.getPreferredSize();  
                    CTMarker ctMarker = anchor.getFrom();  
                    String picIndex = String.valueOf(sheetNum) + "_"  
                            + ctMarker.getRow() + "_" + ctMarker.getCol();  
                    sheetIndexPicMap.put(picIndex, pic.getPictureData());  
                }  
            }  
        }  
        return sheetIndexPicMap;  
    }  
      
    public static void printImg(Map<String, PictureData> map,String excelName) throws IOException {  
        if(map!=null&&map.size()>0){
            Object key[] = map.keySet().toArray();  
            for (int i = 0; i < map.size(); i++) {  
                // 获取图片流  
                PictureData pic = map.get(key[i]);  
                // 获取图片索引  
                String picName = key[i].toString();  
                // 获取图片格式  
                String ext = pic.suggestFileExtension();  
                  
                byte[] data = pic.getData();  
                File file =new File(ExcelConstants.EXCEL_MIDDLEPATH_PIC+excelName+"/");    
                if (!file.exists()){
                	file.mkdirs();
                }
                FileOutputStream out = new FileOutputStream(ExcelConstants.EXCEL_MIDDLEPATH_PIC +excelName+ "/"+picName + "." + ext);  
                out.write(data);  
                out.close();  
            } 
        }
          
    }  
    
    
}
